select count(1) from qd_course where center_id = 2;

select * from qd_courseorder where center_id=2;
select * from qd_transrecord where center_id = 2;
select * from qd_orderpromotions where center_id = 2;
select * from qd_coursefree where center_id = 2;

delete from qd_coursefree where center_id = 2;
commit;

delete from qd_courseorder where center_id=2;
commit;

/**清除自动扣费纪录**/
delete from qd_transrecord where center_id = 2 and inout = 1;
commit;

delete from qd_orderpromotions where center_id = 2;
commit;

--select * from qd_courseorder where center_id=2;
delete from qd_courseorder where center_id = 2;
commit;

update qd_courseorder set paid = 0 where center_id = 2;
commit;

update qd_courseorder set state = 'queuing' where id = 1292;
commit;
/**清除自动扣费纪录**/



select * from qd_student where center_id =2;

update qd_student set mobile = '18620419955' where id = 129;
commit;

select * from qd_member where mobile = '18620419955'; 
update qd_member set mobile = '18620419955' where mobile = '119'; 
commit;

select * from qd_memberstudent where student_id = 129;
select * from qd_member where id = 392;

delete from qd_memberstudent where student_id = 129 and member_id = 392;
commit;

select * from qd_student where mobile= '18620419955';

select * from qd_student where id = 129;
update qd_student set mobile = '1862041995' where id = 129;
commit;

select count(1) from qd_courseorder where course_id = 240 and state = 'ordered';

select * from qd_course where center_id = 2 order by create_date desc;

select * from sys_user;
select * from qd_courseorder order by  desc;
select * from qd_courseorder where state = 'ordered' and create_date > sysdate;

select * from qd_course where ondate>sysdate and center_id = 2;

select trunc(sysdate) from dual;

select * from qd_message order by sent_time desc;

select  from qd_courseorder;

select * from qd_member where mobile = '18620419955';


select rownum as "rank", t.id as "id", t.type as "type", t.course_id as "courseId", 
t.state as "state", t.student_id as "studentId", c.id as "contractId", c.member_id as "memberId", 
c1.price_type as "priceType", c1.price as "price", c1.center_id as "centerId" 
from (
select case when state = 'ordered' then 0 when state = 'queuing' then 1 end as orderby, 
id,type,course_id,student_id,state,create_date from qd_courseorder where type = 0 and course_id = 169
and state not in ('canceled','abort') 
) t 
left join qd_contract c on t.student_id = c.student_id and c.status = 0 
left join qd_course c1 on t.course_id = c1.id order by t.orderby asc,t.create_date asc

select * from qd_transrecord where center_id = 2;
delete from qd_transrecord where id = 1044;
commit;

select 
a.id AS "courseId",
a.state AS "state",
b.type AS "type",
b.ondate AS "ondate",
b.theme AS "theme",
b.serial AS "serial",
b.clevel AS "clevel",
b.section As "section",
c.money As "money"
from qd_courseorder a
left join qd_course b on a.course_id = b.id
left join qd_transrecord c on a.id = c.courseorder_id
left join qd_contract d on a.student_id = d.student_id and d.status = 0 
where b.type = 0 and c.inout = 1
and a.center_id = 2

select * from qd_course where center_id = 2;
select * from qd_courseorder where center_id = 2;
select * from qd_transrecord where courseorder_id is not null and courseorder_id = 1405 and inout = 0 and center_id=2;

select * from qd_course where id = 266;

select max(id) from qd_course;
select seq_qd_course_id.nextval from dual;
commit;

select * from qd_courseorder where center_id = 2;
select * from qd_course where center_id = 2;
update qd_course set del_flag = '0' where id = 289;
commit;

select 
		t.id as "id",
		t.type as "type",
		t.course_id as "courseId",
		t.state as "state",
		t.student_id as "studentId",
		c.id as "contractId",
		c.member_id as "memberId",
		c1.price_type as "priceType",
		c1.price as "price",
		c1.center_id as "centerId"
		from 
		(
		  select id,type,course_id,student_id,state,create_date 
		  from qd_courseorder
		  where type = 0 and course_id = 289 and del_flag = 0 and state not in ('canceled')
		) t
		left join qd_contract c on t.student_id = c.student_id and c.status = 0
		left join qd_course c1 on t.course_id = c1.id
    
    select max(id) from sys_dict;
    
SELECT a.id        AS "id",
	  	a.student_id     AS "studentId",
	  	b.name           AS "student.name",
	  	m.name           AS "member.name",
	  	m.mobile         AS "member.mobile",
	  	c.id             AS "course.id",
	  	c.serial         AS "course.serial",
	  	c.clevel         AS "course.clevel",
	  	c.section        AS "course.section",
	  	a.courseorder_id AS "courseorderId",
	  	a.signtime       AS "signtime",
	  	a.state          AS "state",
	  	a.remarks        AS "remarks",
	  	a.create_by      AS "createBy.id",
	  	a.create_date    AS "createDate",
	  	a.update_by      AS "updateBy.id",
	  	a.update_date    AS "updateDate",
	  	a.del_flag       AS "delFlag",
	  	co.id            AS "courseOrder.id",
	  	co.state         AS "courseOrder.state",
      (select count(1) from qd_siginin where student_id = b.id and state = -1) AS "absentCount"
		FROM qd_courseorder co
		LEFT JOIN qd_course c ON co.course_id = c.id
		LEFT JOIN qd_siginin a ON a.courseorder_id = co.id
		LEFT JOIN qd_student b ON co.student_id = b.id
		LEFT JOIN qd_contract c1 ON co.student_id = c1.student_id and c1.status = 0
		LEFT JOIN qd_member m ON m.id = c1.member_id
    
    select * from qd_contract where id = 109
    select * from qd_member where id = 208;
    update qd_member set mobile = '18613143450' where id = 208;
    commit;
    
    update qd_student set mobile = '18613143450' where id = 127;
    commit;
    
    select 
		t.id as "id",
		t.type as "type",
		t.course_id as "courseId",
		t.state as "state",
		t.student_id as "studentId",
		c.id as "contractId",
		c.member_id as "memberId",
		c1.price_type as "priceType",
		c1.price as "price",
		c1.center_id as "centerId",
	    nvl(tt.money,0) as "money",
	    nvl(tp.leaveCount,0) as "leaveCount",
	    nvl(tp.abortCount,0) as "abortCount"
			from 
			(
			  select id,type,course_id,student_id,state,create_date 
			  from qd_courseorder
			  where type = 0 and course_id = 292 and del_flag = 0 and state not in ('canceled')
			) t
			left join qd_contract c on t.student_id = c.student_id and c.status = 0
			left join qd_course c1 on t.course_id = c1.id
	    left join
	    (
	      select courseorder_id, sum(case when inout = 0 then -money else money end) as money 
	      from qd_transrecord 
	      where courseorder_id in (select id from qd_courseorder where course_id = 292)
	      group by courseorder_id
	    ) tt on tt.courseorder_id = t.id
	    left join
	    (
	      select courseorder_id,sum(leaveCount) as leaveCount,sum(abortCount) as abortCount from 
	      (
	        select  pr.courseorder_id, p.type,
	        (case when p.type = 'leave' then 1 else 0 end) AS leaveCount,
	        (case when p.type = 'abort' then 1 else 0 end) AS abortCount 
	        from qd_privrecord pr
	        left join qd_privilege p on pr.privilege_id = p.id
	        where pr.courseorder_id in (select id from qd_courseorder where course_id = 292)
	      ) t
	      group by courseorder_id
	    ) tp on tp.courseorder_id = t.id
   
select * from sys_dict where type = 'trans_type' order by value;
select * from sys_dict where type = 'trans_way';

select * from qd_course where id = 292;
update qd_course set del_flag = 0 where id = 292;
commit;
select seq_qd_privrecord_id.nextval from dual 
select * from qd_privilege where contract_id = 109;

select * from QD_PRIVRECORD where student_id in ( 127,129);
select * from qd_transrecord where member_id = 0;

    select 
		rank() over (order by t.orderby asc,t.create_date asc) as "rank", 
		t.id as "id",
		t.type as "type",
		t.course_id as "courseId",
		t.state as "state",
		t.student_id as "studentId",
		c.id as "contractId",
		c.member_id as "memberId",
		c1.price_type as "priceType",
		c1.price as "price",
		c1.center_id as "centerId"
		from 
		(
		  select 
		        case when state = 'ordered' then 0 when state = 'queuing' then 1 end as orderby, 
		        id,type,course_id,student_id,state,create_date 
		  from qd_courseorder
		  where type = 0 and course_id = 293 and paid = 0 and state in ('queuing','ordered')
		) t
		left join qd_contract c on t.student_id = c.student_id and c.status = 0
		left join qd_course c1 on t.course_id = c1.id
		order by t.orderby asc,t.create_date asc
    
    select * from qd_attach;
    select * from sys_role_permissions;
    
    select * from qd_album;
    select * from qd_comment;